import openpyexcel
from collections import Counter

BOX_MAX_FILES_COUNT = 2  # 一个箱子最多装多少档案
INPUT_FILE = '人员档案.xlsx'
OUTPUT_FILE = '人员档案1.xlsx'


def is_all_rows_moved():
    for row_number in range(2, sheet1_max_row + 1):
        if sheet1.cell(row_number, 4).value is None:
            return False
    return True


def save_sheet2(wb, box_count_dict):
    sheet2 = wb["Sheet2"]
    sheet2_max_row = sheet2.max_row
    for row_number in range(2, sheet2_max_row + 1):
        box = sheet2.cell(row_number, 1).value
        sheet2.cell(row_number, 2).value = box_count_dict[box]
    wb.save(OUTPUT_FILE)
    print(f'已保存{OUTPUT_FILE}')


if __name__ == '__main__':
    wb = openpyexcel.load_workbook(INPUT_FILE)

    sheet1 = wb["Sheet1"]
    sheet1_max_row = sheet1.max_row

    # print(max_row)
    rows_iter = sheet1.iter_rows(min_col=1, min_row=2,
                                 max_col=4, max_row=sheet1_max_row)
    cells_list = [[cell.value for cell in row] for row in rows_iter]
    # print(cells_list)
    cells1_list = [cells[0] for cells in cells_list]
    # print(cells1_list)
    box_count_dict = Counter(cells1_list)
    # print(box_count_dict)
    cells4_list = [cells[3] for cells in cells_list]
    # print(cells4_list)

    sn = 0
    while not is_all_rows_moved():
        for row_number in range(2, sheet1_max_row + 1):
            if sheet1.cell(row_number, 4).value is not None:
                continue
            # print(sheet1.cell(row_number, 4).value)
            old_box = sheet1.cell(row_number, 1).value
            new_box = sheet1.cell(row_number, 2).value
            moved = False
            if new_box in box_count_dict:
                if box_count_dict[new_box] >= BOX_MAX_FILES_COUNT:
                    continue
                else:
                    box_count_dict[old_box] -= 1
                    box_count_dict[new_box] += 1
                    moved = True
            else:
                box_count_dict[old_box] -= 1
                box_count_dict[new_box] = 1
                moved = True
            if moved:
                sn += 1
                sheet1.cell(row_number, 4).value = sn
                # print(sheet1.cell(row_number, 4).value)
        cells4_list = [cells[3] for cells in cells_list]
        # print(cells4_list)
    save_sheet2(wb, box_count_dict)
